if exists (select 1 from sysobjects where name = 'get_personferiencolumndienst' and type = 'P')
begin
   drop procedure get_personferiencolumndienst
   print 'Procedure: get_personferiencolumndienst deleted ...'
end
go
create procedure get_personferiencolumndienst(
  @prsid             int = 1,
  @atid              int = 1,
  @jahrid            int = 1
)
as
begin
  set nocount on

   select datum = dp.Datum,
          dienstid = d.DienstID,
          name = d.Name,
          typwert = at.typwert
     from ArbeitsTage at
     left outer join ArbeitsPlan ap
       on ap.ATID = at.ATID
    inner join Dienstplan dp
       on dp.PlanID = ap.PlanID
      and dp.PrsID = @prsid
     left outer join Dienst d
       on dp.DienstID = d.DienstID
    where at.JahrID = @jahrid
      and at.ATID = @atid
      and d.Typ = 2 -- ferien

end
go
print 'Procedure: get_personferiencolumndienst done ...'
go

grant exec on get_personferiencolumndienst to prsadmins with grant option
go
grant exec on get_personferiencolumndienst to prsusers
go

